
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
  <head>
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Conditional Expressions &#8212; Django 2.2.12.dev20200304094918 documentation</title>
    <link rel="stylesheet" href="../../_static/default.css" type="text/css" />
    <link rel="stylesheet" href="../../_static/pygments.css" type="text/css" />
    <script type="text/javascript" id="documentation_options" data-url_root="../../" src="../../_static/documentation_options.js"></script>
    <script type="text/javascript" src="../../_static/jquery.js"></script>
    <script type="text/javascript" src="../../_static/underscore.js"></script>
    <script type="text/javascript" src="../../_static/doctools.js"></script>
    <script type="text/javascript" src="../../_static/language_data.js"></script>
    <link rel="index" title="Index" href="../../genindex.html" />
    <link rel="search" title="Search" href="../../search.html" />
    <link rel="next" title="Database Functions" href="database-functions.html" />
    <link rel="prev" title="Query Expressions" href="expressions.html" />



 
<script type="text/javascript" src="../../templatebuiltins.js"></script>
<script type="text/javascript">
(function($) {
    if (!django_template_builtins) {
       // templatebuiltins.js missing, do nothing.
       return;
    }
    $(document).ready(function() {
        // Hyperlink Django template tags and filters
        var base = "../templates/builtins.html";
        if (base == "#") {
            // Special case for builtins.html itself
            base = "";
        }
        // Tags are keywords, class '.k'
        $("div.highlight\\-html\\+django span.k").each(function(i, elem) {
             var tagname = $(elem).text();
             if ($.inArray(tagname, django_template_builtins.ttags) != -1) {
                 var fragment = tagname.replace(/_/, '-');
                 $(elem).html("<a href='" + base + "#" + fragment + "'>" + tagname + "</a>");
             }
        });
        // Filters are functions, class '.nf'
        $("div.highlight\\-html\\+django span.nf").each(function(i, elem) {
             var filtername = $(elem).text();
             if ($.inArray(filtername, django_template_builtins.tfilters) != -1) {
                 var fragment = filtername.replace(/_/, '-');
                 $(elem).html("<a href='" + base + "#" + fragment + "'>" + filtername + "</a>");
             }
        });
    });
})(jQuery);</script>

  </head><body>

    <div class="document">
  <div id="custom-doc" class="yui-t6">
    <div id="hd">
      <h1><a href="../../index.html">Django 2.2.12.dev20200304094918 documentation</a></h1>
      <div id="global-nav">
        <a title="Home page" href="../../index.html">Home</a>  |
        <a title="Table of contents" href="../../contents.html">Table of contents</a>  |
        <a title="Global index" href="../../genindex.html">Index</a>  |
        <a title="Module index" href="../../py-modindex.html">Modules</a>
      </div>
      <div class="nav">
    &laquo; <a href="expressions.html" title="Query Expressions">previous</a>
     |
    <a href="../index.html" title="API Reference" accesskey="U">up</a>
   |
    <a href="database-functions.html" title="Database Functions">next</a> &raquo;</div>
    </div>

    <div id="bd">
      <div id="yui-main">
        <div class="yui-b">
          <div class="yui-g" id="ref-models-conditional-expressions">
            
  <div class="section" id="s-conditional-expressions">
<span id="conditional-expressions"></span><h1>Conditional Expressions<a class="headerlink" href="#conditional-expressions" title="Permalink to this headline">¶</a></h1>
<p>Conditional expressions let you use <a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#if" title="(in Python v3.8)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">if</span></code></a> … <a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#elif" title="(in Python v3.8)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">elif</span></code></a> …
<a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#else" title="(in Python v3.8)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">else</span></code></a> logic within filters, annotations, aggregations, and updates. A
conditional expression evaluates a series of conditions for each row of a
table and returns the matching result expression. Conditional expressions can
also be combined and nested like other <a class="reference internal" href="expressions.html"><span class="doc">expressions</span></a>.</p>
<div class="section" id="s-the-conditional-expression-classes">
<span id="the-conditional-expression-classes"></span><h2>The conditional expression classes<a class="headerlink" href="#the-conditional-expression-classes" title="Permalink to this headline">¶</a></h2>
<p>We’ll be using the following model in the subsequent examples:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db</span> <span class="k">import</span> <span class="n">models</span>

<span class="k">class</span> <span class="nc">Client</span><span class="p">(</span><span class="n">models</span><span class="o">.</span><span class="n">Model</span><span class="p">):</span>
    <span class="n">REGULAR</span> <span class="o">=</span> <span class="s1">&#39;R&#39;</span>
    <span class="n">GOLD</span> <span class="o">=</span> <span class="s1">&#39;G&#39;</span>
    <span class="n">PLATINUM</span> <span class="o">=</span> <span class="s1">&#39;P&#39;</span>
    <span class="n">ACCOUNT_TYPE_CHOICES</span> <span class="o">=</span> <span class="p">[</span>
        <span class="p">(</span><span class="n">REGULAR</span><span class="p">,</span> <span class="s1">&#39;Regular&#39;</span><span class="p">),</span>
        <span class="p">(</span><span class="n">GOLD</span><span class="p">,</span> <span class="s1">&#39;Gold&#39;</span><span class="p">),</span>
        <span class="p">(</span><span class="n">PLATINUM</span><span class="p">,</span> <span class="s1">&#39;Platinum&#39;</span><span class="p">),</span>
    <span class="p">]</span>
    <span class="n">name</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">50</span><span class="p">)</span>
    <span class="n">registered_on</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">DateField</span><span class="p">()</span>
    <span class="n">account_type</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span>
        <span class="n">max_length</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span>
        <span class="n">choices</span><span class="o">=</span><span class="n">ACCOUNT_TYPE_CHOICES</span><span class="p">,</span>
        <span class="n">default</span><span class="o">=</span><span class="n">REGULAR</span><span class="p">,</span>
    <span class="p">)</span>
</pre></div>
</div>
<div class="section" id="s-when">
<span id="when"></span><h3><code class="docutils literal notranslate"><span class="pre">When</span></code><a class="headerlink" href="#when" title="Permalink to this headline">¶</a></h3>
<dl class="class">
<dt id="django.db.models.expressions.When">
<em class="property">class </em><code class="descname">When</code>(<em>condition=None</em>, <em>then=None</em>, <em>**lookups</em>)<a class="reference internal" href="../../_modules/django/db/models/expressions.html#When"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#django.db.models.expressions.When" title="Permalink to this definition">¶</a></dt>
<dd></dd></dl>

<p>A <code class="docutils literal notranslate"><span class="pre">When()</span></code> object is used to encapsulate a condition and its result for use
in the conditional expression. Using a <code class="docutils literal notranslate"><span class="pre">When()</span></code> object is similar to using
the <a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.filter" title="django.db.models.query.QuerySet.filter"><code class="xref py py-meth docutils literal notranslate"><span class="pre">filter()</span></code></a> method. The condition can
be specified using <a class="reference internal" href="querysets.html#field-lookups"><span class="std std-ref">field lookups</span></a> or
<a class="reference internal" href="querysets.html#django.db.models.Q" title="django.db.models.Q"><code class="xref py py-class docutils literal notranslate"><span class="pre">Q</span></code></a> objects. The result is provided using the <code class="docutils literal notranslate"><span class="pre">then</span></code>
keyword.</p>
<p>Some examples:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">F</span><span class="p">,</span> <span class="n">Q</span><span class="p">,</span> <span class="n">When</span>
<span class="gp">&gt;&gt;&gt; </span><span class="c1"># String arguments refer to fields; the following two examples are equivalent:</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">When</span><span class="p">(</span><span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">GOLD</span><span class="p">,</span> <span class="n">then</span><span class="o">=</span><span class="s1">&#39;name&#39;</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">When</span><span class="p">(</span><span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">GOLD</span><span class="p">,</span> <span class="n">then</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;name&#39;</span><span class="p">))</span>
<span class="gp">&gt;&gt;&gt; </span><span class="c1"># You can use field lookups in the condition</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">datetime</span> <span class="k">import</span> <span class="n">date</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">When</span><span class="p">(</span><span class="n">registered_on__gt</span><span class="o">=</span><span class="n">date</span><span class="p">(</span><span class="mi">2014</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">),</span>
<span class="gp">... </span>     <span class="n">registered_on__lt</span><span class="o">=</span><span class="n">date</span><span class="p">(</span><span class="mi">2015</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">),</span>
<span class="gp">... </span>     <span class="n">then</span><span class="o">=</span><span class="s1">&#39;account_type&#39;</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="c1"># Complex conditions can be created using Q objects</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">When</span><span class="p">(</span><span class="n">Q</span><span class="p">(</span><span class="n">name__startswith</span><span class="o">=</span><span class="s2">&quot;John&quot;</span><span class="p">)</span> <span class="o">|</span> <span class="n">Q</span><span class="p">(</span><span class="n">name__startswith</span><span class="o">=</span><span class="s2">&quot;Paul&quot;</span><span class="p">),</span>
<span class="gp">... </span>     <span class="n">then</span><span class="o">=</span><span class="s1">&#39;name&#39;</span><span class="p">)</span>
</pre></div>
</div>
<p>Keep in mind that each of these values can be an expression.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p>Since the <code class="docutils literal notranslate"><span class="pre">then</span></code> keyword argument is reserved for the result of the
<code class="docutils literal notranslate"><span class="pre">When()</span></code>, there is a potential conflict if a
<a class="reference internal" href="instances.html#django.db.models.Model" title="django.db.models.Model"><code class="xref py py-class docutils literal notranslate"><span class="pre">Model</span></code></a> has a field named <code class="docutils literal notranslate"><span class="pre">then</span></code>. This can be
resolved in two ways:</p>
<div class="last highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">When</span><span class="p">(</span><span class="n">then__exact</span><span class="o">=</span><span class="mi">0</span><span class="p">,</span> <span class="n">then</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">When</span><span class="p">(</span><span class="n">Q</span><span class="p">(</span><span class="n">then</span><span class="o">=</span><span class="mi">0</span><span class="p">),</span> <span class="n">then</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="s-case">
<span id="case"></span><h3><code class="docutils literal notranslate"><span class="pre">Case</span></code><a class="headerlink" href="#case" title="Permalink to this headline">¶</a></h3>
<dl class="class">
<dt id="django.db.models.expressions.Case">
<em class="property">class </em><code class="descname">Case</code>(<em>*cases</em>, <em>**extra</em>)<a class="reference internal" href="../../_modules/django/db/models/expressions.html#Case"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#django.db.models.expressions.Case" title="Permalink to this definition">¶</a></dt>
<dd></dd></dl>

<p>A <code class="docutils literal notranslate"><span class="pre">Case()</span></code> expression is like the <a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#if" title="(in Python v3.8)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">if</span></code></a> … <a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#elif" title="(in Python v3.8)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">elif</span></code></a> …
<a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#else" title="(in Python v3.8)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">else</span></code></a> statement in <code class="docutils literal notranslate"><span class="pre">Python</span></code>. Each <code class="docutils literal notranslate"><span class="pre">condition</span></code> in the provided
<code class="docutils literal notranslate"><span class="pre">When()</span></code> objects is evaluated in order, until one evaluates to a
truthful value. The <code class="docutils literal notranslate"><span class="pre">result</span></code> expression from the matching <code class="docutils literal notranslate"><span class="pre">When()</span></code> object
is returned.</p>
<p>A simple example:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="go">&gt;&gt;&gt;</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">datetime</span> <span class="k">import</span> <span class="n">date</span><span class="p">,</span> <span class="n">timedelta</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">Case</span><span class="p">,</span> <span class="n">CharField</span><span class="p">,</span> <span class="n">Value</span><span class="p">,</span> <span class="n">When</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">create</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">name</span><span class="o">=</span><span class="s1">&#39;Jane Doe&#39;</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">REGULAR</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">registered_on</span><span class="o">=</span><span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">36</span><span class="p">))</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">create</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">name</span><span class="o">=</span><span class="s1">&#39;James Smith&#39;</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">GOLD</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">registered_on</span><span class="o">=</span><span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">5</span><span class="p">))</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">create</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">name</span><span class="o">=</span><span class="s1">&#39;Jack Black&#39;</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">PLATINUM</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">registered_on</span><span class="o">=</span><span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">10</span> <span class="o">*</span> <span class="mi">365</span><span class="p">))</span>
<span class="gp">&gt;&gt;&gt; </span><span class="c1"># Get the discount for each Client based on the account type</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">discount</span><span class="o">=</span><span class="n">Case</span><span class="p">(</span>
<span class="gp">... </span>        <span class="n">When</span><span class="p">(</span><span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">GOLD</span><span class="p">,</span> <span class="n">then</span><span class="o">=</span><span class="n">Value</span><span class="p">(</span><span class="s1">&#39;5%&#39;</span><span class="p">)),</span>
<span class="gp">... </span>        <span class="n">When</span><span class="p">(</span><span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">PLATINUM</span><span class="p">,</span> <span class="n">then</span><span class="o">=</span><span class="n">Value</span><span class="p">(</span><span class="s1">&#39;10%&#39;</span><span class="p">)),</span>
<span class="gp">... </span>        <span class="n">default</span><span class="o">=</span><span class="n">Value</span><span class="p">(</span><span class="s1">&#39;0%&#39;</span><span class="p">),</span>
<span class="gp">... </span>        <span class="n">output_field</span><span class="o">=</span><span class="n">CharField</span><span class="p">(),</span>
<span class="gp">... </span>    <span class="p">),</span>
<span class="gp">... </span><span class="p">)</span><span class="o">.</span><span class="n">values_list</span><span class="p">(</span><span class="s1">&#39;name&#39;</span><span class="p">,</span> <span class="s1">&#39;discount&#39;</span><span class="p">)</span>
<span class="go">&lt;QuerySet [(&#39;Jane Doe&#39;, &#39;0%&#39;), (&#39;James Smith&#39;, &#39;5%&#39;), (&#39;Jack Black&#39;, &#39;10%&#39;)]&gt;</span>
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">Case()</span></code> accepts any number of <code class="docutils literal notranslate"><span class="pre">When()</span></code> objects as individual arguments.
Other options are provided using keyword arguments. If none of the conditions
evaluate to <code class="docutils literal notranslate"><span class="pre">TRUE</span></code>, then the expression given with the <code class="docutils literal notranslate"><span class="pre">default</span></code> keyword
argument is returned. If a <code class="docutils literal notranslate"><span class="pre">default</span></code> argument isn’t provided, <code class="docutils literal notranslate"><span class="pre">None</span></code> is
used.</p>
<p>If we wanted to change our previous query to get the discount based on how long
the <code class="docutils literal notranslate"><span class="pre">Client</span></code> has been with us, we could do so using lookups:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">a_month_ago</span> <span class="o">=</span> <span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">30</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">a_year_ago</span> <span class="o">=</span> <span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">365</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="c1"># Get the discount for each Client based on the registration date</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">discount</span><span class="o">=</span><span class="n">Case</span><span class="p">(</span>
<span class="gp">... </span>        <span class="n">When</span><span class="p">(</span><span class="n">registered_on__lte</span><span class="o">=</span><span class="n">a_year_ago</span><span class="p">,</span> <span class="n">then</span><span class="o">=</span><span class="n">Value</span><span class="p">(</span><span class="s1">&#39;10%&#39;</span><span class="p">)),</span>
<span class="gp">... </span>        <span class="n">When</span><span class="p">(</span><span class="n">registered_on__lte</span><span class="o">=</span><span class="n">a_month_ago</span><span class="p">,</span> <span class="n">then</span><span class="o">=</span><span class="n">Value</span><span class="p">(</span><span class="s1">&#39;5%&#39;</span><span class="p">)),</span>
<span class="gp">... </span>        <span class="n">default</span><span class="o">=</span><span class="n">Value</span><span class="p">(</span><span class="s1">&#39;0%&#39;</span><span class="p">),</span>
<span class="gp">... </span>        <span class="n">output_field</span><span class="o">=</span><span class="n">CharField</span><span class="p">(),</span>
<span class="gp">... </span>    <span class="p">)</span>
<span class="gp">... </span><span class="p">)</span><span class="o">.</span><span class="n">values_list</span><span class="p">(</span><span class="s1">&#39;name&#39;</span><span class="p">,</span> <span class="s1">&#39;discount&#39;</span><span class="p">)</span>
<span class="go">&lt;QuerySet [(&#39;Jane Doe&#39;, &#39;5%&#39;), (&#39;James Smith&#39;, &#39;0%&#39;), (&#39;Jack Black&#39;, &#39;10%&#39;)]&gt;</span>
</pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Remember that the conditions are evaluated in order, so in the above
example we get the correct result even though the second condition matches
both Jane Doe and Jack Black. This works just like an <a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#if" title="(in Python v3.8)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">if</span></code></a> …
<a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#elif" title="(in Python v3.8)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">elif</span></code></a> … <a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#else" title="(in Python v3.8)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">else</span></code></a> statement in <code class="docutils literal notranslate"><span class="pre">Python</span></code>.</p>
</div>
<p><code class="docutils literal notranslate"><span class="pre">Case()</span></code> also works in a <code class="docutils literal notranslate"><span class="pre">filter()</span></code> clause. For example, to find gold
clients that registered more than a month ago and platinum clients that
registered more than a year ago:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">a_month_ago</span> <span class="o">=</span> <span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">30</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">a_year_ago</span> <span class="o">=</span> <span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">365</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">registered_on__lte</span><span class="o">=</span><span class="n">Case</span><span class="p">(</span>
<span class="gp">... </span>        <span class="n">When</span><span class="p">(</span><span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">GOLD</span><span class="p">,</span> <span class="n">then</span><span class="o">=</span><span class="n">a_month_ago</span><span class="p">),</span>
<span class="gp">... </span>        <span class="n">When</span><span class="p">(</span><span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">PLATINUM</span><span class="p">,</span> <span class="n">then</span><span class="o">=</span><span class="n">a_year_ago</span><span class="p">),</span>
<span class="gp">... </span>    <span class="p">),</span>
<span class="gp">... </span><span class="p">)</span><span class="o">.</span><span class="n">values_list</span><span class="p">(</span><span class="s1">&#39;name&#39;</span><span class="p">,</span> <span class="s1">&#39;account_type&#39;</span><span class="p">)</span>
<span class="go">&lt;QuerySet [(&#39;Jack Black&#39;, &#39;P&#39;)]&gt;</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="s-advanced-queries">
<span id="advanced-queries"></span><h2>Advanced queries<a class="headerlink" href="#advanced-queries" title="Permalink to this headline">¶</a></h2>
<p>Conditional expressions can be used in annotations, aggregations, lookups, and
updates. They can also be combined and nested with other expressions. This
allows you to make powerful conditional queries.</p>
<div class="section" id="s-conditional-update">
<span id="conditional-update"></span><h3>Conditional update<a class="headerlink" href="#conditional-update" title="Permalink to this headline">¶</a></h3>
<p>Let’s say we want to change the <code class="docutils literal notranslate"><span class="pre">account_type</span></code> for our clients to match
their registration dates. We can do this using a conditional expression and the
<a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.update" title="django.db.models.query.QuerySet.update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">update()</span></code></a> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">a_month_ago</span> <span class="o">=</span> <span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">30</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">a_year_ago</span> <span class="o">=</span> <span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">365</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="c1"># Update the account_type for each Client from the registration date</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">update</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">account_type</span><span class="o">=</span><span class="n">Case</span><span class="p">(</span>
<span class="gp">... </span>        <span class="n">When</span><span class="p">(</span><span class="n">registered_on__lte</span><span class="o">=</span><span class="n">a_year_ago</span><span class="p">,</span>
<span class="gp">... </span>             <span class="n">then</span><span class="o">=</span><span class="n">Value</span><span class="p">(</span><span class="n">Client</span><span class="o">.</span><span class="n">PLATINUM</span><span class="p">)),</span>
<span class="gp">... </span>        <span class="n">When</span><span class="p">(</span><span class="n">registered_on__lte</span><span class="o">=</span><span class="n">a_month_ago</span><span class="p">,</span>
<span class="gp">... </span>             <span class="n">then</span><span class="o">=</span><span class="n">Value</span><span class="p">(</span><span class="n">Client</span><span class="o">.</span><span class="n">GOLD</span><span class="p">)),</span>
<span class="gp">... </span>        <span class="n">default</span><span class="o">=</span><span class="n">Value</span><span class="p">(</span><span class="n">Client</span><span class="o">.</span><span class="n">REGULAR</span><span class="p">)</span>
<span class="gp">... </span>    <span class="p">),</span>
<span class="gp">... </span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">values_list</span><span class="p">(</span><span class="s1">&#39;name&#39;</span><span class="p">,</span> <span class="s1">&#39;account_type&#39;</span><span class="p">)</span>
<span class="go">&lt;QuerySet [(&#39;Jane Doe&#39;, &#39;G&#39;), (&#39;James Smith&#39;, &#39;R&#39;), (&#39;Jack Black&#39;, &#39;P&#39;)]&gt;</span>
</pre></div>
</div>
</div>
<div class="section" id="s-conditional-aggregation">
<span id="s-id1"></span><span id="conditional-aggregation"></span><span id="id1"></span><h3>Conditional aggregation<a class="headerlink" href="#conditional-aggregation" title="Permalink to this headline">¶</a></h3>
<p>What if we want to find out how many clients there are for each
<code class="docutils literal notranslate"><span class="pre">account_type</span></code>? We can use the <code class="docutils literal notranslate"><span class="pre">filter</span></code> argument of <a class="reference internal" href="querysets.html#aggregation-functions"><span class="std std-ref">aggregate
functions</span></a> to achieve this:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="c1"># Create some more Clients first so we can have something to count</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">create</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">name</span><span class="o">=</span><span class="s1">&#39;Jean Grey&#39;</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">REGULAR</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">registered_on</span><span class="o">=</span><span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">())</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">create</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">name</span><span class="o">=</span><span class="s1">&#39;James Bond&#39;</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">PLATINUM</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">registered_on</span><span class="o">=</span><span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">())</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">create</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">name</span><span class="o">=</span><span class="s1">&#39;Jane Porter&#39;</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">PLATINUM</span><span class="p">,</span>
<span class="gp">... </span>    <span class="n">registered_on</span><span class="o">=</span><span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">())</span>
<span class="gp">&gt;&gt;&gt; </span><span class="c1"># Get counts for each value of account_type</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">Count</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Client</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">aggregate</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">regular</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s1">&#39;pk&#39;</span><span class="p">,</span> <span class="nb">filter</span><span class="o">=</span><span class="n">Q</span><span class="p">(</span><span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">REGULAR</span><span class="p">)),</span>
<span class="gp">... </span>    <span class="n">gold</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s1">&#39;pk&#39;</span><span class="p">,</span> <span class="nb">filter</span><span class="o">=</span><span class="n">Q</span><span class="p">(</span><span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">GOLD</span><span class="p">)),</span>
<span class="gp">... </span>    <span class="n">platinum</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s1">&#39;pk&#39;</span><span class="p">,</span> <span class="nb">filter</span><span class="o">=</span><span class="n">Q</span><span class="p">(</span><span class="n">account_type</span><span class="o">=</span><span class="n">Client</span><span class="o">.</span><span class="n">PLATINUM</span><span class="p">)),</span>
<span class="gp">... </span><span class="p">)</span>
<span class="go">{&#39;regular&#39;: 2, &#39;gold&#39;: 1, &#39;platinum&#39;: 3}</span>
</pre></div>
</div>
<p>This aggregate produces a query with the SQL 2003 <code class="docutils literal notranslate"><span class="pre">FILTER</span> <span class="pre">WHERE</span></code> syntax
on databases that support it:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="s1">&#39;id&#39;</span><span class="p">)</span> <span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">account_type</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span> <span class="k">as</span> <span class="n">regular</span><span class="p">,</span>
       <span class="k">count</span><span class="p">(</span><span class="s1">&#39;id&#39;</span><span class="p">)</span> <span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">account_type</span><span class="o">=</span><span class="mi">2</span><span class="p">)</span> <span class="k">as</span> <span class="n">gold</span><span class="p">,</span>
       <span class="k">count</span><span class="p">(</span><span class="s1">&#39;id&#39;</span><span class="p">)</span> <span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">account_type</span><span class="o">=</span><span class="mi">3</span><span class="p">)</span> <span class="k">as</span> <span class="n">platinum</span>
<span class="k">FROM</span> <span class="n">clients</span><span class="p">;</span>
</pre></div>
</div>
<p>On other databases, this is emulated using a <code class="docutils literal notranslate"><span class="pre">CASE</span></code> statement:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">account_type</span><span class="o">=</span><span class="mi">1</span> <span class="k">THEN</span> <span class="n">id</span> <span class="k">ELSE</span> <span class="k">null</span><span class="p">)</span> <span class="k">as</span> <span class="n">regular</span><span class="p">,</span>
       <span class="k">count</span><span class="p">(</span><span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">account_type</span><span class="o">=</span><span class="mi">2</span> <span class="k">THEN</span> <span class="n">id</span> <span class="k">ELSE</span> <span class="k">null</span><span class="p">)</span> <span class="k">as</span> <span class="n">gold</span><span class="p">,</span>
       <span class="k">count</span><span class="p">(</span><span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">account_type</span><span class="o">=</span><span class="mi">3</span> <span class="k">THEN</span> <span class="n">id</span> <span class="k">ELSE</span> <span class="k">null</span><span class="p">)</span> <span class="k">as</span> <span class="n">platinum</span>
<span class="k">FROM</span> <span class="n">clients</span><span class="p">;</span>
</pre></div>
</div>
<p>The two SQL statements are functionally equivalent but the more explicit
<code class="docutils literal notranslate"><span class="pre">FILTER</span></code> may perform better.</p>
</div>
</div>
</div>


          </div>
        </div>
      </div>
      
        
          <div class="yui-b" id="sidebar">
            
      <div class="sphinxsidebar" role="navigation" aria-label="main navigation">
        <div class="sphinxsidebarwrapper">
  <h3><a href="../../contents.html">Table of Contents</a></h3>
  <ul>
<li><a class="reference internal" href="#">Conditional Expressions</a><ul>
<li><a class="reference internal" href="#the-conditional-expression-classes">The conditional expression classes</a><ul>
<li><a class="reference internal" href="#when"><code class="docutils literal notranslate"><span class="pre">When</span></code></a></li>
<li><a class="reference internal" href="#case"><code class="docutils literal notranslate"><span class="pre">Case</span></code></a></li>
</ul>
</li>
<li><a class="reference internal" href="#advanced-queries">Advanced queries</a><ul>
<li><a class="reference internal" href="#conditional-update">Conditional update</a></li>
<li><a class="reference internal" href="#conditional-aggregation">Conditional aggregation</a></li>
</ul>
</li>
</ul>
</li>
</ul>

  <h4>Previous topic</h4>
  <p class="topless"><a href="expressions.html"
                        title="previous chapter">Query Expressions</a></p>
  <h4>Next topic</h4>
  <p class="topless"><a href="database-functions.html"
                        title="next chapter">Database Functions</a></p>
  <div role="note" aria-label="source link">
    <h3>This Page</h3>
    <ul class="this-page-menu">
      <li><a href="../../_sources/ref/models/conditional-expressions.txt"
            rel="nofollow">Show Source</a></li>
    </ul>
   </div>
<div id="searchbox" style="display: none" role="search">
  <h3>Quick search</h3>
    <div class="searchformwrapper">
    <form class="search" action="../../search.html" method="get">
      <input type="text" name="q" />
      <input type="submit" value="Go" />
      <input type="hidden" name="check_keywords" value="yes" />
      <input type="hidden" name="area" value="default" />
    </form>
    </div>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
        </div>
      </div>
              <h3>Last update:</h3>
              <p class="topless">Mar 04, 2020</p>
          </div>
        
      
    </div>

    <div id="ft">
      <div class="nav">
    &laquo; <a href="expressions.html" title="Query Expressions">previous</a>
     |
    <a href="../index.html" title="API Reference" accesskey="U">up</a>
   |
    <a href="database-functions.html" title="Database Functions">next</a> &raquo;</div>
    </div>
  </div>

      <div class="clearer"></div>
    </div>
  </body>
</html>